package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.ConfigProperties;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IStationExShiftStatisService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;

/**
 * 完整性校验-出口流水对帐报表数据统计
 */
@Service("StationExShiftStatisService")
public class StationExShiftStatisService implements IStationExShiftStatisService {
    private Connection connPre = null;

    @Override
    public void syncRecord() throws Exception {
        Integer m_statisDays = 6;
        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
            m_statisDays = Integer.parseInt(configProperties.getSquadDays());

            String sql = "update TB_STATIS_STATION_EXSHIFT set STATION_ETC_TOTALCNT=0,STATION_ETC_TOTALFEE=0 \n" +
                    "where SQUAD_DATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') and CONFIRM_STATE is null ";
            Integer count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]重置ETC出口工班表统计数据(条):" + count);

            //1.统计ETC出口工班表数据, 获取6天前的数据
            sql = "MERGE INTO TB_STATIS_STATION_EXSHIFT a \n" +
                    "USING ( \n" +
                    "  select tmp.*,s.ROAD_ID from ( \n" +
                    "  select x.STATION_ID,x.SQUAD_DATE,x.SQUAD_NO,x.LANE_NO,x.SQUARD_TYPE,nvl(sum(x.TOTAL_COUNT),0) TOTAL_COUNT,\n" +
                    "  nvl(sum(x.TOTAL_MONEY),0) TOTAL_MONEY,nvl(sum(x.CASE_MONEY),0) CASE_MONEY,nvl(sum(x.NONCASH_MONEY),0) NONCASH_MONEY,\n" +
                    "  nvl(sum(x.PAYCARD_MONEY),0) PAYCARD_MONEY,nvl(sum(x.ETC_TOTALCOUNT),0) ETC_TOTALCOUNT,nvl(sum(x.ETC_TOTALMONEY),0) ETC_TOTALMONEY \n" +
                    "  from TB_SYNC_EXSHIFT x,(\n" +
                    "    select STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,SQUARD_TYPE,OPERATORNO,max(SEQ_ID) SEQ_ID \n" +
                    "    from TB_SYNC_EXSHIFT group by STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,SQUARD_TYPE,OPERATORNO) y \n" +
                    "  where x.SQUARD_TYPE='etc' and x.STATION_ID=y.STATION_ID and x.SQUAD_DATE=y.SQUAD_DATE and x.SQUAD_NO=y.SQUAD_NO and x.LANE_NO=y.LANE_NO \n" +
                    "  and x.SQUARD_TYPE=y.SQUARD_TYPE and nvl(x.OPERATORNO,-51)=nvl(y.OPERATORNO,-51) and x.SEQ_ID=y.SEQ_ID \n" +
                    "  and x.SQUAD_DATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') \n" +
                    "  group by x.STATION_ID,x.SQUAD_DATE,x.SQUAD_NO,x.LANE_NO,x.SQUARD_TYPE \n" +
                    "  ) tmp left join tb_charge_station s on tmp.STATION_ID=s.STATION_ID \n" +
                    ") b \n" +
                    "ON (a.STATION_ID=b.STATION_ID and a.SQUAD_DATE=b.SQUAD_DATE and a.SQUAD_NO=b.SQUAD_NO and a.LANE_NO=b.LANE_NO) \n" +
                    "WHEN MATCHED THEN \n" +
                    "  UPDATE SET a.STATION_MTC_TOTALCNT=decode(b.SQUARD_TYPE,'mtc',nvl(b.TOTAL_COUNT,0),0),\n" +
                    "  a.STATION_MTC_TOTALFEE=decode(b.SQUARD_TYPE,'mtc',nvl(b.TOTAL_MONEY,0),0),\n" +
                    "  a.STATION_CASH_FEE=nvl(b.CASE_MONEY,0),a.STATION_NONCASH_FEE=nvl(b.NONCASH_MONEY,0),a.STATION_PAYCARD_FEE=nvl(b.PAYCARD_MONEY,0),\n" +
                    "  a.STATION_ETC_TOTALCNT=decode(b.SQUARD_TYPE,'mtc',nvl(b.ETC_TOTALCOUNT,0),'etc',nvl(b.TOTAL_COUNT,0),0),\n" +
                    "  a.STATION_ETC_TOTALFEE=decode(b.SQUARD_TYPE,'mtc',nvl(b.ETC_TOTALMONEY,0),'etc',nvl(b.TOTAL_MONEY,0),0),UPDATE_TIME=sysdate WHERE a.CONFIRM_STATE is null \n" +
                    "WHEN NOT MATCHED THEN \n" +
                    "  INSERT (SEQ_ID,ROAD_ID,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,\n" +
                    "  STATION_MTC_TOTALCNT,STATION_MTC_TOTALFEE,STATION_CASH_FEE,STATION_NONCASH_FEE,STATION_PAYCARD_FEE,\n" +
                    "  STATION_ETC_TOTALCNT,STATION_ETC_TOTALFEE,MTC_TOTALCNT,MTC_TOTALFEE,ETC_TOTALCNT,ETC_TOTALFEE,RECEIVE_TIME) \n" +
                    "  VALUES (SEQ_STATIS_EXSHIFT1_ID.nextval,b.ROAD_ID,b.STATION_ID,b.SQUAD_DATE,b.SQUAD_NO,b.LANE_NO,\n" +
                    "  decode(b.SQUARD_TYPE,'mtc',nvl(b.TOTAL_COUNT,0),0),decode(b.SQUARD_TYPE,'mtc',nvl(b.TOTAL_MONEY,0),0),\n" +
                    "  nvl(b.CASE_MONEY,0),nvl(b.NONCASH_MONEY,0),nvl(b.PAYCARD_MONEY,0),\n" +
                    "  decode(b.SQUARD_TYPE,'mtc',nvl(b.ETC_TOTALCOUNT,0),'etc',nvl(b.TOTAL_COUNT,0),0),\n" +
                    "  decode(b.SQUARD_TYPE,'mtc',nvl(b.ETC_TOTALMONEY,0),'etc',nvl(b.TOTAL_MONEY,0),0),0,0,0,0,sysdate) ";
            count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]统计ETC出口工班表数据(条):" + count);

            //2.统计MTC出口工班表数据, 获取6天前的数据
            sql = "MERGE INTO TB_STATIS_STATION_EXSHIFT a \n" +
                    "USING ( \n" +
                    "  select tmp.*,s.ROAD_ID from ( \n" +
                    "  select x.STATION_ID,x.SQUAD_DATE,x.SQUAD_NO,x.LANE_NO,x.SQUARD_TYPE,nvl(sum(x.TOTAL_COUNT),0) TOTAL_COUNT,\n" +
                    "  nvl(sum(x.TOTAL_MONEY),0) TOTAL_MONEY,nvl(sum(x.CASE_MONEY),0) CASE_MONEY,nvl(sum(x.NONCASH_MONEY),0) NONCASH_MONEY,\n" +
                    "  nvl(sum(x.PAYCARD_MONEY),0) PAYCARD_MONEY,nvl(sum(x.ETC_TOTALCOUNT),0) ETC_TOTALCOUNT,nvl(sum(x.ETC_TOTALMONEY),0) ETC_TOTALMONEY \n" +
                    "  from TB_SYNC_EXSHIFT x,(\n" +
                    "    select STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,SQUARD_TYPE,OPERATORNO,max(SEQ_ID) SEQ_ID \n" +
                    "    from TB_SYNC_EXSHIFT group by STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,SQUARD_TYPE,OPERATORNO) y \n" +
                    "  where x.SQUARD_TYPE='mtc' and x.STATION_ID=y.STATION_ID and x.SQUAD_DATE=y.SQUAD_DATE and x.SQUAD_NO=y.SQUAD_NO and x.LANE_NO=y.LANE_NO \n" +
                    "  and x.SQUARD_TYPE=y.SQUARD_TYPE and nvl(x.OPERATORNO,-51)=nvl(y.OPERATORNO,-51) and x.SEQ_ID=y.SEQ_ID \n" +
                    "  and x.SQUAD_DATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') \n" +
                    "  group by x.STATION_ID,x.SQUAD_DATE,x.SQUAD_NO,x.LANE_NO,x.SQUARD_TYPE \n" +
                    "  ) tmp left join tb_charge_station s on tmp.STATION_ID=s.STATION_ID \n" +
                    ") b \n" +
                    "ON (a.STATION_ID=b.STATION_ID and a.SQUAD_DATE=b.SQUAD_DATE and a.SQUAD_NO=b.SQUAD_NO and a.LANE_NO=b.LANE_NO) \n" +
                    "WHEN MATCHED THEN \n" +
                    "  UPDATE SET a.STATION_MTC_TOTALCNT=decode(b.SQUARD_TYPE,'mtc',nvl(b.TOTAL_COUNT,0),0),\n" +
                    "  a.STATION_MTC_TOTALFEE=decode(b.SQUARD_TYPE,'mtc',nvl(b.TOTAL_MONEY,0),0),\n" +
                    "  a.STATION_CASH_FEE=nvl(b.CASE_MONEY,0),a.STATION_NONCASH_FEE=nvl(b.NONCASH_MONEY,0),a.STATION_PAYCARD_FEE=nvl(b.PAYCARD_MONEY,0),\n" +
                    "  a.STATION_ETC_TOTALCNT=nvl(a.STATION_ETC_TOTALCNT,0)+decode(b.SQUARD_TYPE,'mtc',nvl(b.ETC_TOTALCOUNT,0),'etc',nvl(b.TOTAL_COUNT,0),0),\n" +
                    "  a.STATION_ETC_TOTALFEE=nvl(a.STATION_ETC_TOTALFEE,0)+decode(b.SQUARD_TYPE,'mtc',nvl(b.ETC_TOTALMONEY,0),'etc',nvl(b.TOTAL_MONEY,0),0),UPDATE_TIME=sysdate WHERE a.CONFIRM_STATE is null \n" +
                    "WHEN NOT MATCHED THEN \n" +
                    "  INSERT (SEQ_ID,ROAD_ID,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,\n" +
                    "  STATION_MTC_TOTALCNT,STATION_MTC_TOTALFEE,STATION_CASH_FEE,STATION_NONCASH_FEE,STATION_PAYCARD_FEE,\n" +
                    "  STATION_ETC_TOTALCNT,STATION_ETC_TOTALFEE,MTC_TOTALCNT,MTC_TOTALFEE,ETC_TOTALCNT,ETC_TOTALFEE,RECEIVE_TIME) \n" +
                    "  VALUES (SEQ_STATIS_EXSHIFT1_ID.nextval,b.ROAD_ID,b.STATION_ID,b.SQUAD_DATE,b.SQUAD_NO,b.LANE_NO,\n" +
                    "  decode(b.SQUARD_TYPE,'mtc',nvl(b.TOTAL_COUNT,0),0),decode(b.SQUARD_TYPE,'mtc',nvl(b.TOTAL_MONEY,0),0),\n" +
                    "  nvl(b.CASE_MONEY,0),nvl(b.NONCASH_MONEY,0),nvl(b.PAYCARD_MONEY,0),\n" +
                    "  decode(b.SQUARD_TYPE,'mtc',nvl(b.ETC_TOTALCOUNT,0),'etc',nvl(b.TOTAL_COUNT,0),0),\n" +
                    "  decode(b.SQUARD_TYPE,'mtc',nvl(b.ETC_TOTALMONEY,0),'etc',nvl(b.TOTAL_MONEY,0),0),0,0,0,0,sysdate) ";
            count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]统计MTC出口工班表数据(条):" + count);

            //3.统计省中心接收其他出口流水数据, 获取6天前的数据
            sql = "MERGE INTO TB_STATIS_STATION_EXSHIFT a \n" +
                    "USING ( \n" +
                    "  select tmp.*,s.ROAD_ID from ( \n" +
                    "    select STATIONNO STATION_ID,SQUADDATE SQUAD_DATE,SQUADNO SQUAD_NO,LANENO LANE_NO,TOTAL_CNT,TOTAL_FEE,NONCASH_FEE from (\n" +
                    "      select STATIONNO,SQUADDATE,SQUADNO,LANENO,COUNT(1) TOTAL_CNT,\n" +
                    "      NVL(SUM(CASE WHEN (VEHNUM NOT IN (0,99) AND ELEPAYMETHOD IN(0,9,100)) THEN FEE ELSE 0 END),0) TOTAL_FEE,\n" +
                    "      SUM(CASE WHEN (VEHNUM NOT IN (0,99) AND ELEPAYMETHOD IN(1,2,3)) THEN FEE ELSE 0 END) NONCASH_FEE \n" +
                    "      from TB_SYNC_TRC_OTU \n" +
                    "      where SQUADDATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') \n" +
                    "      group by STATIONNO,SQUADDATE,SQUADNO,LANENO )\n" +
                    "  ) tmp left join tb_charge_station s on tmp.STATION_ID=s.STATION_ID\n" +
                    ") b \n" +
                    "ON (a.STATION_ID=b.STATION_ID and a.SQUAD_DATE=b.SQUAD_DATE and a.SQUAD_NO=b.SQUAD_NO and a.LANE_NO=b.LANE_NO) \n" +
                    "WHEN MATCHED THEN \n" +
                    "  UPDATE SET MTC_TOTALCNT = nvl(TOTAL_CNT,0),MTC_TOTALFEE=nvl(TOTAL_FEE,0)+nvl(NONCASH_FEE,0),CASH_FEE=nvl(TOTAL_FEE,0),\n" +
                    "  NONCASH_FEE=nvl(NONCASH_FEE,0),UPDATE_TIME=sysdate WHERE a.CONFIRM_STATE is null \n" +
                    "WHEN NOT MATCHED THEN \n" +
                    "  INSERT (SEQ_ID,ROAD_ID,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,\n" +
                    "  STATION_MTC_TOTALCNT,STATION_MTC_TOTALFEE,STATION_CASH_FEE,STATION_NONCASH_FEE,STATION_PAYCARD_FEE,\n" +
                    "  STATION_ETC_TOTALCNT,STATION_ETC_TOTALFEE,MTC_TOTALCNT,MTC_TOTALFEE,CASH_FEE,NONCASH_FEE,ETC_TOTALCNT,ETC_TOTALFEE,RECEIVE_TIME) \n" +
                    "  VALUES (SEQ_STATIS_EXSHIFT1_ID.nextval,b.ROAD_ID,b.STATION_ID,b.SQUAD_DATE,b.SQUAD_NO,b.LANE_NO,\n" +
                    "  0,0,0,0,0,0,0,nvl(b.TOTAL_CNT,0),nvl(b.TOTAL_FEE,0)+nvl(b.NONCASH_FEE,0),nvl(b.TOTAL_FEE,0),nvl(b.NONCASH_FEE,0),0,0,sysdate) ";
            count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]统计省中心接收其他出口流水数据(条):" + count);

            //4.统计省中心ETC接收流水数据, 获取6天前的数据
            sql = "MERGE INTO TB_STATIS_STATION_EXSHIFT a \n" +
                    "USING ( \n" +
                    "  select tmp.*,s.ROAD_ID from ( \n" +
                    "    SELECT STATIONNO STATION_ID,SQUADDATE SQUAD_DATE,SQUADNO SQUAD_NO,EXTOLLLANEID LANE_NO,\n" +
                    "    COUNT(1) ETC_TOTALCNT,SUM(decode(datatype,1,fee,0)) ETC_TOTALFEE,SUM(DECODE(datatype,2,fee,0)) PAYCARD_FEE FROM ( \n" +
                    "      select STATIONNO,SQUADDATE,SQUADNO,EXTOLLLANEID,\n" +
                    "      (case when (VEHNUM not in (0,99) and ELEPAYMETHOD not in (7,8,107,108)) then FEE else 0 end) as FEE,\n" +
                    "      TRANSPAYTYPE as DATATYPE,VEHNUM,ELEPAYMETHOD \n" +
                    "      from TB_SYNC_TRC_EXETCPU \n" +
                    "      where SQUADDATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') \n" +
                    "    ) \n" +
                    "    GROUP BY STATIONNO,SQUADDATE,SQUADNO,EXTOLLLANEID \n" +
                    "  ) tmp left join tb_charge_station s on tmp.STATION_ID=s.STATION_ID \n" +
                    ") b \n" +
                    "ON (a.STATION_ID=b.STATION_ID and a.SQUAD_DATE=b.SQUAD_DATE and a.SQUAD_NO=b.SQUAD_NO and a.LANE_NO=b.LANE_NO) \n" +
                    "WHEN MATCHED THEN \n" +
                    "  UPDATE SET ETC_TOTALCNT = nvl(ETC_TOTALCNT,0),ETC_TOTALFEE = nvl(ETC_TOTALFEE,0),\n" +
                    "  PAYCARD_FEE = nvl(PAYCARD_FEE,0),UPDATE_TIME=sysdate WHERE a.CONFIRM_STATE is null \n" +
                    "WHEN NOT MATCHED THEN \n" +
                    "  INSERT (SEQ_ID,ROAD_ID,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,ETC_TOTALCNT,ETC_TOTALFEE,PAYCARD_FEE,RECEIVE_TIME,UPDATE_TIME) \n" +
                    "  VALUES (SEQ_STATIS_EXSHIFT1_ID.nextval,b.ROAD_ID,b.STATION_ID,b.SQUAD_DATE,b.SQUAD_NO,b.LANE_NO,\n" +
                    "  nvl(b.ETC_TOTALCNT,0),nvl(b.ETC_TOTALFEE,0)+nvl(b.PAYCARD_FEE,0),nvl(b.PAYCARD_FEE,0),sysdate,sysdate) ";
            count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]统计省中心ETC接收流水数据(条):" + count);

            connPre.commit();
            XxlJobLogger.log("任务执行完成");
        } catch (Exception e) {
            if (connPre != null) {
                try {
                    XxlJobLogger.log("出现运行异常：" + e);
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                } catch (Exception e1) {
                    e1.printStackTrace();
                    throw new Exception("回滚失败");
                }
            }
            throw e;
        } finally {
            if (connPre != null) {
                try {
                    XxlJobLogger.log("提交成功.");
                    connPre.setAutoCommit(true);
                    connPre.close();
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new Exception("关闭数据库连接失败");
                }
            }
        }
    }
}
